set more off
capture log close
global dir "directory"
log using "$dir/acamort/code/0Z_MJW_Controls.log", replace
/***********************************************************************
PROGRAM:	10_MJW_Controls.do
PURPOSE:    This program compiles control variables for QJE R&R
DATE:		January 21, 2020
UPDATED:	January 28, 2020 to add control vars compiled by SM
************************************************************************/
global final "$dir/local/finaldata"
global temp "$dir/local/tempdata"
global indata "$dir/acamort/indata"
global output "$dir/acamort/output"
global build "$dir/acamort/build"
global pop "$dir/local/countypop"

***A) STATE-LEVEL DATASET
**unemployment rate, source: UKCPR National Welfare Data, 1980-2018
*http://ukcpr.org/resources/national-welfare-data
import excel using "$indata/UKCPR_National_Welfare_Data_Update_122019", first ///
sh("Data") clear
keep state_fips year Unemploymentrate
keep if inrange(year,2008,2018)
label var state_fips "State FIPS code"
label var year "Year"
rename Unemploymentrate stunemp
label var stunemp "State-year unemployment rate"
save "$build/statevars", replace

**triplicate status as of 1996, source: Alpert et al. 2019 NBER WP
*California, Idaho, Illinois, New York, and Texas
gen triplicate=0
replace triplicate=1 if inlist(state_fips,6,16,17,36,48)
label var triplicate "Initial triplicate state"
save "$build/statevars", replace

** other opioid related policies discussed and controlled for in sensitivity
** analyses by Alpert et al. 2019 NBER WP
** note: for all policy changes, consider in effect in a given year if in place before July
*enactment of a PDMP, source: Horwitz et al. 2018 NBER WP, Table 2
gen pdmp=0
replace pdmp=1 if inlist(state_fips,1,4,6,8,9,15,16,17,18,19,21,22,23,25,26, ///
27,28,32,35,36,37,38,39,40,41,42,44,45,47,48,49,50,51,54,56)
replace pdmp=1 if state_fips==2 & year>2008
replace pdmp=1 if state_fips==5 & year>2012
replace pdmp=1 if state_fips==10 & year>2011
replace pdmp=1 if state_fips==11 & year>2013
replace pdmp=1 if state_fips==12 & year>2010
replace pdmp=1 if state_fips==13 & year>2011
replace pdmp=1 if state_fips==20 & year>2008
replace pdmp=1 if state_fips==24 & year>2011
replace pdmp=1 if state_fips==29 & year>2017
replace pdmp=1 if state_fips==30 & year>2011
replace pdmp=1 if state_fips==31 & year>2011
replace pdmp=1 if state_fips==33 & year>2011
replace pdmp=1 if state_fips==34 & year>2009
replace pdmp=1 if state_fips==46 & year>2009
replace pdmp=1 if state_fips==53 & year>2011
replace pdmp=1 if state_fips==55 & year>2009
label var pdmp "State PDMP enactment"
bysort year: su pdmp
*note: Alpert et al. 2019 also controlled for enacted a modern, electronic system,
*source: Horwitz et al. 2018 NBER WP, Table 2, however, only one state experienced
*a change during our study period (MA) and it was an excluded state

*adoption of "must access" PDMPs, source: Prescription Drug Abuse Policy System (PDAPS)
*downloaded on 1/15/20 at http://pdaps.org/datasets/prescription-monitoring-program-laws-1408223416-1502818373
*laws from January 1, 1998 to July 1, 2016
*NOTE: incomplete for 2017 and 2018
gen mustaccess=0
replace mustaccess=1 if state_fips==6 & year>2016
replace mustaccess=1 if state_fips==9 & year>2015
replace mustaccess=1 if state_fips==18 & year>2014
replace mustaccess=1 if state_fips==21 & year>2012
replace mustaccess=1 if state_fips==22 & year>2014
replace mustaccess=1 if state_fips==23 & year>2016
replace mustaccess=1 if state_fips==25 & year>2014
replace mustaccess=1 if state_fips==32 & year>2015
replace mustaccess=1 if state_fips==33 & year>2015
replace mustaccess=1 if state_fips==34 & year>2015
replace mustaccess=1 if state_fips==35 & year>2012
replace mustaccess=1 if state_fips==36 & year>2013
replace mustaccess=1 if state_fips==39 & year>2015
replace mustaccess=1 if state_fips==40 & year>2015
replace mustaccess=1 if state_fips==42 & year>2014
replace mustaccess=1 if state_fips==44 & year>2015
replace mustaccess=1 if state_fips==47 & year>2012
replace mustaccess=1 if state_fips==50 & year>2014
replace mustaccess=1 if state_fips==51 & year>2015
replace mustaccess=1 if state_fips==54 & year>2011
replace mustaccess=1 if state_fips==56 & year>2016
label var mustaccess "State must access PDMPs"

*adoption of pain clinic regulations, source: PDAPS, Pain Management Clinic Laws,
*downloaded on 1/15/20 at http://pdaps.org/datasets/pain-management-clinic-laws
*laws from January 1, 2006 through June 1, 2018
gen painlaw=0
replace painlaw=1 if inlist(state_fips,22)
replace painlaw=1 if state_fips==1 & year>2012
replace painlaw=1 if state_fips==12 & year>2010
replace painlaw=1 if state_fips==13 & year>2013
replace painlaw=1 if state_fips==21 & year>2012
replace painlaw=1 if state_fips==28 & year>2010
replace painlaw=1 if state_fips==39 & year>2010
replace painlaw=1 if state_fips==47 & year>2010
replace painlaw=1 if state_fips==48 & year>2009
replace painlaw=1 if state_fips==54 & year>2011
replace painlaw=1 if state_fips==55 & year>2015
label var painlaw "State pain clinic regulation"

*medical marijuana law enactment date, source: Powell et al. 2018, Table A1
*for laws passed through 2014; updated with more recent law changes from
*National Conference for State Legislatures accessed on 1/15/2020:
*https://www.ncsl.org/research/health/state-medical-marijuana-laws.aspx,
*as well as a review of statutory language when needed
gen medmj=0
replace medmj=1 if inlist(state_fips,2,6,8,15,23,26,30,32,35,41,44,50,53)
replace medmj=1 if state_fips==4 & year>2010
replace medmj=1 if state_fips==9 & year>2011
replace medmj=1 if state_fips==10 & year>2010
replace medmj=1 if state_fips==11 & year>2009
replace medmj=1 if state_fips==17 & year>2013
replace medmj=1 if state_fips==24 & year>2013
replace medmj=1 if state_fips==25 & year>2012
replace medmj=1 if state_fips==27 & year>2013
replace medmj=1 if state_fips==33 & year>2012
replace medmj=1 if state_fips==34 & year>2009
replace medmj=1 if state_fips==36 & year>2013
replace medmj=1 if state_fips==5 & year>2016
replace medmj=1 if state_fips==38 & year>2016
replace medmj=1 if state_fips==39 & year>2015
replace medmj=1 if state_fips==42 & year>2015
replace medmj=1 if state_fips==54 & year>2016
label var medmj "State MMJ Policy"

*legal/operational medical marijuana dispensaries, source: Powell et al. 2018, Table A1
*for legal/operations through 2014; note we follow these authors and define the policy
*measure as state/year when dispensaries are both legally protected and operational;
*updated with more recent law changes from NCSL accessed on 1/15/2020:
*https://www.ncsl.org/research/health/state-medical-marijuana-laws.aspx,
*as well as a review of statutory language when needed
*also follow method described in Powell et al. 2018 to identify date of first
*legal dispensary operating
gen dispmj=0
replace dispmj=1 if inlist(state_fips,6)
replace dispmj=1 if state_fips==4 & year>2012
replace dispmj=1 if state_fips==8 & year>2009
replace dispmj=1 if state_fips==9 & year>2014
replace dispmj=1 if state_fips==11 & year>2012
replace dispmj=1 if state_fips==23 & year>2010
replace dispmj=1 if state_fips==32 & year>2014
replace dispmj=1 if state_fips==34 & year>2012
replace dispmj=1 if state_fips==35 & year>2009
replace dispmj=1 if state_fips==41 & year>2013
replace dispmj=1 if state_fips==44 & year>2012
replace dispmj=1 if state_fips==50 & year>2012
replace dispmj=1 if state_fips==30 & year>2016
replace dispmj=1 if state_fips==42 & year>2017
replace dispmj=1 if state_fips==54 & year>2017
label var dispmj "State legal/open MMJ dispensaries"

su
save "$build/statevars", replace

**Drug OD deaths from SM
use "$indata/drugods_state/drugods_state", clear
keep statecode deaths population cruderate year
rename statecode state_fips
rename deaths drugodst
rename population drugodpopst
rename cruderate drugodratest
label var drugodst "Drug OD deaths state"
label var drugodpopst "Drug OD pop state"
label var drugodratest "Drug OD crude rate state"
merge 1:1 state_fips year using "$build/statevars", nogen
save "$build/statevars", replace

*Opioid deaths from SM
use "$indata/opioid_bystate/opioid_state", clear
destring state, gen(state_fips)
drop stname state
merge 1:1 state_fips year using "$build/statevars", nogen
su
save "$build/statevars", replace

***B) COUNTY-LEVEL DATASET
***county level unemployment rates from BLS
***Labor force data by county, annual averages for 2008-2013
***downloaded here https://www.bls.gov/lau/#cntyaa on 1/15/20
import excel using "$indata/unemp_county/laucnty08.xlsx", cellra(A7:J3223) clear
rename B state_fips
rename C county_fips
rename E year
rename J unemp_rate
keep state_fips county_fips year unemp_rate
save "$build/countyvars", replace
import excel using "$indata/unemp_county/laucnty09.xlsx", cellra(A7:J3223) clear
rename B state_fips
rename C county_fips
rename E year
rename J unemp_rate
keep state_fips county_fips year unemp_rate
append using "$build/countyvars"
save "$build/countyvars", replace
forval x=10/18 {
	import excel using "$indata/unemp_county/laucnty`x'.xlsx", cellra(A7:J3225) clear
	rename B state_fips
	rename C county_fips
	rename E year
	rename J unemp_rate
	keep state_fips county_fips year unemp_rate
	append using "$build/countyvars"
	save "$build/countyvars", replace
}
egen countyfips=concat(state_fips county_fips)
destring countyfips state_fips year, replace
drop county_fips
su
bysort year: su unemp_rate
label var unemp_rate "Unemployment rate (%)"
label var countyfips "County FIPS code"
label var state_fips "State FIPS code"
label var year "Year"
drop if state_fips>56
save "$build/countyvars", replace

***county poverty rates and median household incomes
*SAIPE Census, downloaded on 1/21/20 here: https://www.census.gov/programs-surveys/saipe/data.html
import excel using "$indata/saipe/est08all.xls", first cellra(A3:AE3197) clear
keep StateFIPS CountyFIPS PovertyEstimateAllAges MedianHouseholdIncome
gen year=2008
tostring CountyFIPS, format(%03.0f) replace
drop if CountyFIPS=="000"
egen countyfips=concat(StateFIPS CountyFIPS)
save "$temp/temp", replace
import excel using "$indata/saipe/est09all.xls", first cellra(A3:AE3198) clear
keep StateFIPS CountyFIPS PovertyEstimateAllAges MedianHouseholdIncome
gen year=2009
tostring CountyFIPS, format(%03.0f) replace
drop if CountyFIPS=="000"
egen countyfips=concat(StateFIPS CountyFIPS)
append using "$temp/temp"
save "$temp/temp", replace
forval x=10/11{
import excel using "$indata/saipe/est`x'all.xls", first cellra(A3:AE3198) clear
keep StateFIPS CountyFIPS PovertyEstimateAllAges MedianHouseholdIncome
gen year=2000+`x'
destring PovertyEstimateAllAges MedianHouseholdIncome, replace
tostring CountyFIPS, format(%03.0f) replace
drop if CountyFIPS=="000"
egen countyfips=concat(StateFIPS CountyFIPS)
append using "$temp/temp"
save "$temp/temp", replace
}
import excel using "$indata/saipe/est12all.xls", first cellra(A3:AE3198) clear
keep StateFIPSCode CountyFIPSCode PovertyEstimateAllAges MedianHouseholdIncome
gen year=2012
destring PovertyEstimateAllAges MedianHouseholdIncome, replace
egen countyfips=concat(StateFIPSCode CountyFIPSCode)
drop if CountyFIPSCode=="000"
append using "$temp/temp"
save "$temp/temp", replace
import excel using "$indata/saipe/est13all.xls", first cellra(A4:AE3199) clear
keep StateFIPSCode CountyFIPSCode PovertyEstimateAllAges MedianHouseholdIncome
gen year=2013
destring PovertyEstimateAllAges MedianHouseholdIncome, replace
egen countyfips=concat(StateFIPSCode CountyFIPSCode)
drop if CountyFIPSCode=="000"
append using "$temp/temp"
save "$temp/temp", replace
forval x=14/18{
import excel using "$indata/saipe/est`x'all.xls", first cellra(A4:AE3198) clear
keep StateFIPSCode CountyFIPSCode PovertyEstimateAllAges MedianHouseholdIncome
gen year=2000+`x'
destring PovertyEstimateAllAges MedianHouseholdIncome, replace
egen countyfips=concat(StateFIPSCode CountyFIPSCode)
drop if CountyFIPSCode=="000"
append using "$temp/temp"
save "$temp/temp", replace
}
rename PovertyEstimateAllAges povrate
rename MedianHouseholdIncome medhhinc
label var povrate "County poverty rate"
label var medhhinc "Median household income"
keep countyfips year povrate medhhinc
destring countyfips, replace
replace countyfips=46113 if countyfips==46102 & inrange(year,2008,2014)
merge 1:1 countyfips year using "$build/countyvars", nogen
save "$build/countyvars", replace

***county uninsurance estimates
*downloaded from SAHIE Census at https://www.census.gov/data/datasets/time-series/demo/sahie/estimates-acs.html
insheet using "$indata/sahie/sahie_2008.csv", nonames clear delimiter(",")
drop if _n<70
rename v1 year
rename v3 statefips
rename v4 countyfips
rename v5 geocat
rename v6 agecat
rename v7 racecat
rename v8 sexcat
rename v9 iprcat
rename v16 pctui
destring sexcat racecat geocat agecat iprcat, replace
keep if sexcat==0 & racecat==0 & geocat==50
keep if inlist(agecat,0,1,3) & inlist(iprcat,0,3)
save "$temp/temp", replace
forval y=2009/2017{
	insheet using "$indata/sahie/sahie_`y'.csv", nonames clear delimiter(",")
	drop if _n<70
	rename v1 year
	rename v3 statefips
	rename v4 countyfips
	rename v5 geocat
	rename v6 agecat
	rename v7 racecat
	rename v8 sexcat
	rename v9 iprcat
	rename v16 pctui
	destring sexcat racecat geocat agecat iprcat, replace
	keep if sexcat==0 & racecat==0 & geocat==50
	keep if inlist(agecat,0,1,3) & inlist(iprcat,0,3)
	append using "$temp/temp"
	save "$temp/temp", replace
}
keep pctui year statefips countyfips iprcat agecat
reshape wide pctui, i(year statefips countyfips iprcat) j(agecat)
rename pctui0 pctui_0_64_
rename pctui1 pctui_18_64_
rename pctui3 pctui_50_64_
reshape wide pctui*, i(year statefips countyfips) j(iprcat)
foreach x in pctui_0_64_ pctui_18_64_ pctui_50_64_ {
	rename `x'0 `x'all
	rename `x'3	`x'l138
}
label var pctui_0_64_all "County uninsured rate, ages0-64, all incomes"
label var pctui_18_64_all "County uninsured rate, ages18-64, all incomes"
label var pctui_50_64_all "County uninsured rate, ages50-64, all incomes"
label var pctui_0_64_l138 "County uninsured rate, ages 0-64, <138%FPL"
label var pctui_18_64_l138 "County uninsured rate, ages18-64, <138%FPL"
label var pctui_50_64_l138 "County uninsured rate, ages 50-64, <138% FPL"
tostring countyfips, format(%03.0f) replace
egen dum=concat(statefips countyfips)
drop statefips countyfips
rename dum countyfips
destring countyfips year, replace
merge 1:1 countyfips year using "$build/countyvars", nogen
save "$build/countyvars", replace

*read-in population estimates from Census
*Source: Annual County Resident Population Estimates by Age, Sex, Race, and
*Hispanic Origin: April 1, 2010 to July 1, 2018 (CC-EST2018-ALLDATA)
*downloaded at:https://www.census.gov/data/tables/time-series/demo/popest/2010s-counties-detail.html
*on January 28 2020; file for 2000 to 2010 from NBER
*https://data.nber.org/data/census-intercensal-county-population-age-sex-race-hispanic.html
insheet using "$pop/cc-est2018-alldata.csv", c clear
tostring county, format(%03.0f) replace
egen countyfips=concat(state county)
destring countyfips, replace
keep if agegrp==0 | inrange(agegrp,5,13)
keep if year>2
replace year=year+2007
collapse (sum) tot_pop tot_female ba_male ba_female wa_male wa_female h_male h_female, by(countyfips year agegrp)
reshape wide tot_pop tot_female ba_male ba_female wa_male wa_female h_female h_male, i(countyfips year) j(agegrp)
gen sharefemale=tot_female0/tot_pop0
gen shareblack=(ba_male0+ba_female0)/tot_pop0
gen sharewhite=(wa_male0+wa_female0)/tot_pop0
gen sharehispanic=(h_male0+h_female0)/tot_pop0
rename tot_pop0 poptotal
save "$temp/temp", replace
use "$pop/coest00intalldata", clear
destring county, gen(countyfips)
keep if agegrp==99 | inrange(agegrp,5,13)
keep if inrange(year,2008,2009)
collapse (sum) tot_pop tot_female ba_male ba_female wa_male wa_female h_male h_female, by(countyfips year agegrp)
reshape wide tot_pop tot_female ba_male ba_female wa_male wa_female h_female h_male, i(countyfips year) j(agegrp)
gen sharefemale=tot_female99/tot_pop99
gen shareblack=(ba_male99+ba_female99)/tot_pop99
gen sharewhite=(wa_male99+wa_female99)/tot_pop99
gen sharehispanic=(h_male99+h_female99)/tot_pop99
rename tot_pop99 poptotal
append using "$temp/temp"
rename tot_pop5 pop20_24
rename tot_pop6 pop25_29
rename tot_pop7 pop30_34
rename tot_pop8 pop35_39
rename tot_pop9 pop40_44
rename tot_pop10 pop45_49
rename tot_pop11 pop50_54
rename tot_pop12 pop55_59
rename tot_pop13 pop60_64
label var sharefemale "Share female population"
label var shareblack "Share black population"
label var sharewhite "Share white population"
label var sharehispanic "Share Hispanic population"
label var poptotal "Total pop count"
label var pop20_24 "Pop count ages 20-24"
label var pop25_29 "Pop count ages 25-29"
label var pop30_34 "Pop count ages 30-34"
label var pop35_39 "Pop count ages 35-39"
label var pop40_44 "Pop count ages 40-44"
label var pop45_49 "Pop count ages 45-49"
label var pop50_54 "Pop count ages 50-54"
label var pop55_59 "Pop count ages 55-59"
label var pop60_64 "Pop count ages 60-64"
keep countyfips year share* pop*
merge 1:1 countyfips year using "$build/countyvars", nogen
save "$build/countyvars", replace

**drug overdose data from SM
use "$indata/drugods/drugods", clear
rename deaths drugodcount
rename population drugodpop
rename cruderate drugodrate
label var drugodcount "Count of drug OD deaths"
label var drugodpop "Population for drug OD death rate"
label var drugodrate "Crude drug OD rate"
drop yearcode
drop county
rename countycode countyfips
merge 1:1 countyfips year using "$build/countyvars", nogen
save "$build/countyvars", replace

**Bartik measures from SM
use "$indata/bartik/bartik", clear
tostring county_fips, format(%03.0f) replace
drop if county_fips=="999"
egen countyfips=concat(state_fips county_fips)
destring countyfips, replace
keep countyfips bartik_allind year
label var bartik_allind "All industry Bartik employment growth measure"
merge 1:1 countyfips year using "$build/countyvars", nogen
save "$build/countyvars", replace

***county centroid latitude and longitude from geoCounty R package
*downloaded from the National Weather Service, https://www.weather.gov/gis/Counties
insheet using "$indata/countycentroids/countyCentroids.csv", names clear
rename fips countyfips
keep countyfips lon lat
rename lon centroidlon
rename lat centroidlat
label var centroidlon "Centroid longitude"
label var centroidlat "Centroid latitute"
bysort countyfips: keep if _n==1
merge 1:m countyfips using "$build/countyvars", keep(using match) nogen
save "$build/countyvars", replace

***county to CZ cross-walk: 1990 Counties to 1990 CZ
*downloaded from David Dorn's website: https://www.ddorn.net/data/cw_cty_czone.zip
*added updates with changes in counties since 1990 using memo on DD's website
*https://www.ddorn.net/data/FIPS_County_Code_Changes.pdf
use "$indata/countyczcrosswalk/cw_cty_czone", clear
rename cty_fips countyfips
replace countyfips=46102 if countyfips==46113
replace countyfips=12086 if countyfips==12025
merge 1:m countyfips using "$build/countyvars", keep(using match) nogen
replace czone=28900 if countyfips==8014
label var czone "Commuting Zone"
label var countyfips "County FIPS code"
save "$build/countyvars", replace

***C) CZ-LEVEL DATASET
**china shock instrument for years 2000-2007 from Autor et al. 2013
use "$indata/Autor_et_al_2013/dta/workfile_china", clear
keep if yr==2000
keep czone statefip d_tradeusch_pw d_tradeotch_pw_lag
rename d_tradeusch_pw tradeusch2000_2007
rename d_tradeotch_pw_lag tradeotch2000_2007
label var tradeusch2000_2007 "Chinese import growth in US, 2000-2007"
label var tradeotch2000_2007 "Instrument using lagged employment, 2000-2007"
save "$build/czonevars", replace

**china shock instrument for years 2000-2014 from Autor et al. 2019
use "$indata/Autor_et_al_2019/dta/workfile9014wwd", clear
keep if yr==2000
keep czone statefip d_impusch_p9 d_impotch_p9_lag
rename d_impusch_p9 tradeusch2000_2014
rename d_impotch_p9_lag tradeotch2000_2014
label var tradeusch2000_2014 "Chinese import growth in US, 2000-2014"
label var tradeotch2000_2014 "Instrument using lagged employment, 2000-2014"
merge 1:1 czone statefip using "$build/czonevars", nogen
label var czone "Commuting Zone"
label var statefip "State FIPS code"
save "$build/czonevars", replace

***D) Combine into one dataset for RDC
use "$build/statevars", replace
merge 1:m state_fips year using "$build/countyvars", nogen
merge m:1 czone using "$build/czonevars", nogen
destring pct*, replace
su
save "$build/controlvars", replace

*checks
collapse opioid* drug* stunemp triplicate pdmp mustaccess painlaw medmj ///
dispmj czone centroidlon centroidlat bartik* pop* share* pct* pov* ///
medhhinc unemp* trade*, by(year)
set more on
foreach x of var _all {
	list year `x'
}
use "$build/controlvars", clear
collapse (count) opioid* drug* stunemp triplicate pdmp mustaccess painlaw medmj ///
dispmj czone centroidlon centroidlat bartik* pop* share* pct* pov* ///
medhhinc unemp* trade*, by(year)
set more on
foreach x of var _all {
	list year `x'
}

*corrected dataset for RDC
use "$build/controlvars", clear
keep poptotal shareblack sharefemale sharehispanic sharewhite year countyfips
su
save "$build/controlvars2", replace
